﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using kit206_A2.model;
using MySql.Data.MySqlClient;
using MySql.Data.Types;

namespace kit206_A2.database_controller
{
    class adapter
    {
        private const string db = "kit206";
        private const string user = "kit206";
        private const string pass = "kit206";
        private const string server = "alacritas.cis.utas.edu.au";
        private static MySqlConnection conn = null;

        //fix convert string to enum
        public static T ParseEnum<T>(string value)
        {
            return (T)Enum.Parse(typeof(T), value);
        }

        //start db connection
        private static MySqlConnection GetConnection()
        {
            if (conn == null)
            {
                //Note: This approach is not thread-safe
                string connectionString = String.Format("Database={0};Data Source={1};User Id={2};Password={3}", db, server, user, pass);
                conn = new MySqlConnection(connectionString);
            }
            return conn;
        }

        //load basic data from db to staff list
        public static List<staff> LoadAll_staff()
        {
            List<staff> staff = new List<staff>();

            MySqlConnection conn = GetConnection();
            MySqlDataReader rdr = null;

            try
            {
                conn.Open();

                MySqlCommand cmd = new MySqlCommand("select id, given_name, family_name, title, campus, category from staff", conn);
                rdr = cmd.ExecuteReader();

                while (rdr.Read())
                {
                    //add each staff from db to the list
                    staff.Add(new staff { ID = rdr.GetInt32("id"), givenName = rdr.GetString("given_name"), familyName=rdr.GetString("family_name"), title = rdr.GetString("title"), campus = ParseEnum<Campus>(rdr.GetString("campus")), category = ParseEnum<Category>(rdr.GetString("category"))});
                }
            }
            //catch error
            catch (MySqlException e)
            {
                Console.WriteLine("Error connecting to database: " + e);
            }
            //result
            finally
            {
                if (rdr != null)
                {
                    rdr.Close();
                }
                if (conn != null)
                {
                    conn.Close();
                }
            }
            //return staff list
            return staff;
        }


        //load staff's detail by id
        public static staff LoadStaff_detaill (int id)
        {
            staff detail = new staff();

            MySqlConnection conn = GetConnection();
            MySqlDataReader rdr = null;

            try
            {
                conn.Open();

                MySqlCommand cmd = new MySqlCommand("select * from staff where id=?id", conn);

                cmd.Parameters.AddWithValue("id", id);
                rdr = cmd.ExecuteReader();

                while (rdr.Read())
                {
                    detail = new staff
                    {
                        ID = rdr.GetInt32("id"),
                        givenName = rdr.GetString("given_name"),
                        familyName = rdr.GetString("family_name"),
                        title = rdr.GetString("title"),
                        campus = ParseEnum<Campus>(rdr.GetString("campus")),
                        room = rdr.GetString("room"),
                        email = rdr.GetString("email"),
                        phone = rdr.GetString("phone"),
                        category = ParseEnum<Category>(rdr.GetString("category")),
                        photo = rdr.GetString("photo"),
                    };
                }
            }
            catch (MySqlException e)
            {
                Console.WriteLine("Error connecting to database: " + e);
            }
            finally
            {
                if (rdr != null)
                {
                    rdr.Close();
                }
                if (conn != null)
                {
                    conn.Close();
                }
            }
            
            return detail;
        }

        //load staff's teaching classes, consultation
        public static void LoadStaff_teaching(staff one_staff)
        { 
            MySqlConnection conn = GetConnection();
            MySqlDataReader rdr = null;
            

            try
            {
                conn.Open();

                //get consultation of the staff by id
                MySqlCommand cmd = new MySqlCommand("select * from consultation where staff_id = ?id", conn);

                cmd.Parameters.AddWithValue("id", one_staff.ID);
                rdr = cmd.ExecuteReader();

                one_staff.Consultations = new List<class_event>();

                while (rdr.Read())
                {
                    one_staff.Consultations.Add(new class_event
                    {
                        Day = ParseEnum<DayOfWeek>(rdr.GetString("day")),
                        Start_time = rdr.GetTimeSpan("start"),
                        End_time = rdr.GetTimeSpan("end"),
                    });
                }
                rdr.Close();


                //get staff teaching classes
                var sql = "SELECT * FROM class,unit,staff where class.staff = ?id and class.unit_code = unit.code and unit.coordinator = staff.id";
                cmd = new MySqlCommand(sql, conn);
                cmd.Parameters.AddWithValue("id", one_staff.ID);
                rdr = cmd.ExecuteReader();

                one_staff.classeTeaching = new List<unit_class>();

                while (rdr.Read())
                {
                    var coordinator_temp = new staff
                    {
                        givenName = rdr.GetString("given_name"),
                        familyName = rdr.GetString("family_name"),
                        title = rdr.GetString("title")
                    };

                    var unit_temp = new unit
                    {
                        unit_code = rdr.GetString("code"),
                        unit_name = rdr.GetString("title"),
                        coordinator = coordinator_temp
                    };


                    one_staff.classeTeaching.Add(new unit_class
                    {
                        unit = unit_temp,
                        campus = ParseEnum<Campus>(rdr.GetString("campus")),
                        Day = ParseEnum<DayOfWeek>(rdr.GetString("day")),
                        Start_time = rdr.GetTimeSpan("start"),
                        End_time = rdr.GetTimeSpan("end"),
                        type = ParseEnum<class_type>(rdr.GetString("type")),
                        room = rdr.GetString("room"),
                        //staff = staff_temp,
                    });
                }
                rdr.Close();

            }
            catch (MySqlException e)
            {
                Console.WriteLine("Error connecting to database: " + e);
            }
            finally
            {
                if (rdr != null)
                {
                    rdr.Close();
                }
                if (conn != null)
                {
                    conn.Close();
                }
            }
            
        }


        //load all class and staff using param of unit code
        public static List<unit_class> LoadClass(unit unit_ , List<unit_class> copy )
        {
            List<unit_class> class_ = new List<unit_class>();

            MySqlConnection conn = GetConnection();
            MySqlDataReader rdr = null;

            try
            {
                conn.Open();

                
                MySqlCommand cmd = new MySqlCommand("SELECT * FROM class, unit ,staff where class.staff=staff.id and class.unit_code = unit.code and class.unit_code = ?unitcode ORDER by unit_code", conn);
                cmd.Parameters.AddWithValue("unitcode", unit_.unit_code);
                rdr = cmd.ExecuteReader();

                while (rdr.Read())
                {

                    var staff = new staff
                    {
                        givenName = rdr.GetString("given_name"),
                        familyName = rdr.GetString("family_name"),
                        title = rdr.GetString("title")
                    };

                    var class_list = new unit_class
                    {
                        unit = unit_,
                        campus = ParseEnum<Campus>(rdr.GetString("campus")),
                        Day = ParseEnum<DayOfWeek>(rdr.GetString("day")),
                        Start_time = rdr.GetTimeSpan("start"),
                        End_time = rdr.GetTimeSpan("end"),
                        type = ParseEnum<class_type>(rdr.GetString("type")),
                        room = rdr.GetString("room"),
                        //staff = staff,

                    };
                    //Console.WriteLine(class_list);

                    class_.Add(class_list);
                    copy.Add(class_list);



                }
            }
            catch (MySqlException e)
            {
                Console.WriteLine("Error connecting to database: " + e);
            }
            finally
            {
                if (rdr != null)
                {
                    rdr.Close();
                }
                if (conn != null)
                {
                    conn.Close();
                }
            }
            
            return copy;
        }

        //load consultation by staff id 
        public static List<class_event> LoadConsultation(int id)
        {
            List<class_event> consultation = new List<class_event>();

            MySqlConnection conn = GetConnection();
            MySqlDataReader rdr = null;

            try
            {
                conn.Open();

                MySqlCommand cmd = new MySqlCommand("select day, start, end from consultation where staff_id=?id", conn);
                cmd.Parameters.AddWithValue("id", id);
                rdr = cmd.ExecuteReader();

                while (rdr.Read())
                {
                    consultation.Add(new class_event
                    {
                        Day = ParseEnum<DayOfWeek>(rdr.GetString(0)),
                        Start_time = rdr.GetTimeSpan(1),
                        End_time = rdr.GetTimeSpan(2)
                    });
                }
            }
            catch (MySqlException e)
            {
                Console.WriteLine("Error connecting to database: " + e);
            }
            finally
            {
                if (rdr != null)
                {
                    rdr.Close();
                }
                if (conn != null)
                {
                    conn.Close();
                }
            }

            return consultation;
        }

    }
}
